package com.mbc.rmt.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.mbc.rmt.data.AddCodeDataList;
import com.mbc.rmt.data.AddCodeNDetail;
import com.mbc.rmt.data.AddCodeNDetailDataList;
import com.mbc.rmt.entities.AddCode;
import com.mbc.rmt.entities.AddCodeRpt;
import com.mbc.common.dao.DAO;
import com.mbc.common.param.CriteriaParams;

public class AddCodeDAOImpls extends DAO<AddCode> implements AddCodeDAO {

	public AddCodeDAOImpls(Connection conn) {
		this.conn = conn;
		this.table_name = "AddCode";
	}

	@Override
	public void create(AddCode entity) throws SQLException {
		query = "insert into " + table_name
				+ " ( AddCodeId, Code, Sender, BranchId) values( ?, ?, ?, ?)";
		pstmt = conn.prepareStatement(query);
		int i = 1;
		pstmt.setLong(i++, entity.getAddCodeId());
		pstmt.setString(i++, entity.getCode());
		pstmt.setString(i++, entity.getSender());
		pstmt.setLong(i++, entity.getBranchId());

		pstmt.executeUpdate();
		pstmt.close();
	}

	@Override
	public void update(AddCode entity) throws SQLException {
		query = "update " + table_name
				+ " set  Code = ?, Sender = ?, BranchId = ?"
				+ " where AddCodeId = " + entity.getAddCodeId();
		pstmt = conn.prepareStatement(query);
		int i = 1;
		pstmt.setString(i++, entity.getCode());
		pstmt.setString(i++, entity.getSender());
		pstmt.setLong(i++, entity.getBranchId());

		pstmt.executeUpdate();
		pstmt.close();
	}

	@Override
	public void delete(long key) throws SQLException {
		query = getDeleteQuery() + " where AddCodeId = " + key;
		pstmt = conn.prepareStatement(query);
		pstmt.executeUpdate();
		pstmt.close();
	}

	@Override
	public AddCode find(long key) throws SQLException {
		AddCode l_AddCode = null;
		query = getSelectQuery() + " where AddCodeId = " + key;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next())
			l_AddCode = readRecord(rs);
		rs.close();
		pstmt.close();
		return l_AddCode;
	}

	@Override
	public ArrayList<AddCode> findAll() throws SQLException {
		ArrayList<AddCode> l_AddCodeList = new ArrayList<AddCode>();
		query = getSelectQuery();
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		while (rs.next()) {
			l_AddCodeList.add(readRecord(rs));
		}
		rs.close();
		pstmt.close();
		return l_AddCodeList;
	}

	@Override
	protected AddCode readRecord(ResultSet rs) throws SQLException {
		AddCode l_entity = new AddCode();
		l_entity.setAddCodeId(rs.getLong("AddCodeId"));
		l_entity.setCode(rs.getString("Code"));
		l_entity.setSender(rs.getString("Sender"));
		l_entity.setBranchId(rs.getLong("BranchId"));
		return l_entity;
	}

	@Override
	public AddCodeDataList findAddCodes(AddCode cri) throws SQLException {
		AddCodeDataList l_DataList = new AddCodeDataList();
		ArrayList<AddCode> l_entityList = new ArrayList<AddCode>();
		
		String l_cri = " and " + cri.getFieldname() + " like N'%" + cri.getKeyword() + "%'"
//				" and (Code like N'%" + cri.getKeyword() + "%'"
//				+ " or Sender like N'%" + cri.getKeyword() + "%')"
				+ " and BranchId = " + cri.getBranchId();
		
		query = "set rowcount "
				+ cri.getSize()
				+ " select * from (select row_number() over(order by AddCodeId) as [rowno], * from "
				+ table_name + " where 1 = 1" + l_cri + ") " + table_name
				+ " where rowno > " + cri.getIndex();
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		while (rs.next()) {
			l_entityList.add(readRecord(rs));
		}
		l_DataList.setEntityList(l_entityList);
		rs.close();
		pstmt.close();
		query = "select count(AddCodeId) from " + table_name + " where 1 = 1"
				+ l_cri;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next()) {
			l_DataList.setTotalRecord(rs.getLong(1));
		}
		rs.close();
		pstmt.close();
		return l_DataList;
	}

	@Override
	public AddCode findByCode(String code, long branchId) throws SQLException {
		AddCode l_AddCode = null;
		query = getSelectQuery() + " where Code = N'" + code
				+ "' and BranchId = " + branchId;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next())
			l_AddCode = readRecord(rs);
		rs.close();
		pstmt.close();
		return l_AddCode;

	}

	@Override
	public AddCodeNDetailDataList findAddCodeNDetails(AddCodeNDetail cri)
			throws SQLException {
		AddCodeNDetailDataList l_DataList = new AddCodeNDetailDataList();
		ArrayList<AddCodeNDetail> l_list = new ArrayList<AddCodeNDetail>();

		/*String l_cri = " and (a.Code like N'%" + cri.getKeyword() + "%'"
		+ " or a.Sender like N'%" + cri.getKeyword() + "%'"
		+ " or b.Receiver like N'%" + cri.getKeyword() + "%'"
		+ " or b.NRC like N'%" + cri.getKeyword() + "%'"
		+ " or b.Address like N'%" + cri.getKeyword() + "%')"
		+ " and a.BranchId = " + cri.getBranchId();*/

		String l_cri = " and " + cri.getFieldname() + " like N'%" + cri.getKeyword() + "%'" 
		+ " and a.BranchId = " + cri.getBranchId();
		
		query = "set rowcount "
				+ cri.getSize()
				+ " select * from (select row_number() over(order by a.AddCodeId desc) as [rowno], a.AddCodeId, a.Code, a.Sender, b.AddCodeDetailId, b.Receiver, b.Nrc, b.Address from "
				+ table_name + " a"
				+ " inner join addcodedetail b on a.addcodeid = b.addcodeid"
				+ " where 1 = 1" + l_cri + ") " + table_name
				+ " where rowno > " + cri.getIndex();
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		while (rs.next()) {
			AddCodeNDetail l_addCodeNDetail = new AddCodeNDetail();
			l_addCodeNDetail.setAddCodeId(rs.getLong("AddCodeId"));
			l_addCodeNDetail.setCode(rs.getString("Code"));
			l_addCodeNDetail.setSender(rs.getString("Sender"));
			l_addCodeNDetail.setAddCodeDetailId(rs.getLong("AddCodeDetailId"));
			l_addCodeNDetail.setReceiver(rs.getString("Receiver"));
			l_addCodeNDetail.setNrc(rs.getString("Nrc"));
			l_addCodeNDetail.setAddress(rs.getString("Address"));
			l_list.add(l_addCodeNDetail);
		}
		l_DataList.setEntityList(l_list);
		rs.close();
		pstmt.close();
		query = "select count(a.AddCodeId) from " + table_name + " a"
				+ " inner join addcodedetail b on a.addcodeid = b.addcodeid"
				+ " where 1 = 1" + l_cri;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next()) {
			l_DataList.setTotalRecord(rs.getLong(1));
		}
		rs.close();
		pstmt.close();

		return l_DataList;
	}

	// MBC Begin

	public ResultSet prepareAddCodeReport(AddCodeRpt info) throws SQLException {
		ResultSet l_rs;
		String l_query = "dbo.sp_cr_AddCoderpt ?, ?, ?";

		CallableStatement l_pstmt = conn.prepareCall(l_query);
		AddCodeRptParameter(l_pstmt, info);
		l_rs = l_pstmt.executeQuery();
		return l_rs;

	}

	private void AddCodeRptParameter(CallableStatement l_pstmt, AddCodeRpt info)
			throws SQLException {
		int i = 1;

		l_pstmt.setString(i++, info.getFromAddCodeID());
		l_pstmt.setString(i++, info.getToAddCodeID());
		l_pstmt.setLong(i++, info.getBranchId());
	}
	
	protected AddCode readRecords(ResultSet rs) throws SQLException {
		AddCode l_entity = new AddCode();
		l_entity.setAddCodeId(rs.getLong("AddCodeId"));
		l_entity.setCode(rs.getString("Code"));
		l_entity.setSender(rs.getString("Sender"));
		l_entity.setAddCodeDetailId(rs.getLong("AddCodeDetailId"));
		l_entity.setReceiver(rs.getString("Receiver"));
		l_entity.setNRC(rs.getString("Nrc"));
		l_entity.setAddress(rs.getString("Address"));
		return l_entity;
	}
	
	protected AddCode readRecordForLatestAddCode(ResultSet rs) throws SQLException {
		AddCode l_entity = new AddCode();
		l_entity.setAddCodeId(rs.getLong("AddCodeId"));
		l_entity.setCode(rs.getString("Code"));
		l_entity.setSender(rs.getString("Sender"));
		l_entity.setReceiver(rs.getString("Receiver"));
		l_entity.setNRC(rs.getString("Nrc"));
		l_entity.setAddress(rs.getString("Address"));
		return l_entity;
	}
	// MBC END

	@Override
	public AddCode getAddCode(String code,long branchId) throws SQLException {
		AddCode l_AddCode = new AddCode();
		query = "select ac.AddCodeId,ac.Code,ac.Sender,"
				+ " acd.AddCodeDetailId,acd.Receiver,acd.NRC,acd.Address "
				+ "from AddCode ac "
				+ " left join AddCodeDetail acd on ac.AddCodeId=acd.AddCodeId"
				+ " where ac.Code = N'" + code + "' and ac.BranchId = " + branchId;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next())
			l_AddCode = readRecords(rs);
		rs.close();
		pstmt.close();
		return l_AddCode;

	}
}
